﻿using Dapper;
using Dapper.Contrib.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Weight.Base;
using Weight.Model;
using Weight.Model.ParamEntity;
using Weight.Model.ResultEntity;

namespace Weight.Dal.CardManager
{
    /// <summary>
    /// 卡号管理
    /// </summary>
   public  class CardManagerDal
    {
        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
       public CardManagerQueryRes List(QueryCardManagerReq req)
        {
            int rowStart = req.PageSize * (req.PageIndex - 1);
            int rowEnd = req.PageSize * req.PageIndex;
            string sql = string.Empty;
            StringBuilder whereBuider = new StringBuilder(" where   IsDel=@IsDel ");
            if (req != null)
            {
                if (!string.IsNullOrEmpty(req.RFNo))
                {
                    req.IsEnd = false;
                    whereBuider.Append(" and  RFNo=@RFNo");
                    whereBuider.Append(" and  IsEnd=@IsEnd");
                }
                else
                {
                    if (req.State != -1)
                    {
                        whereBuider.Append(" and  State=@State");
                    }

                    whereBuider.Append(" and  IsEnd=@IsEnd");

                    if (!string.IsNullOrEmpty(req.RFStatus))
                    {
                        whereBuider.Append(" and  RFStatus=@RFStatus");
                    }

                    if (!string.IsNullOrEmpty(req.StartDate) && !string.IsNullOrEmpty(req.EndDate))
                    {
                        whereBuider.Append(" and (Convert(DateTime,CreateDate)>=Convert(DateTime,@StartDate)")
                            .Append(" and Convert(DateTime,CreateDate)<=Convert(DateTime,@EndDate)").Append(")");
                    }

                    if (!string.IsNullOrEmpty(req.PlateNumber))
                    {
                        req.PlateNumber = "%" + req.PlateNumber + "%";
                        whereBuider.Append(" and PlateNumber like @PlateNumber ");
                    }

                    if (!string.IsNullOrEmpty(req.ContractNo))
                    {
                        whereBuider.Append(" and ContractNo=@ContractNo ");
                    }

                    if (!string.IsNullOrEmpty(req.Material))
                    {
                        req.Material = "%" + req.Material + "%";
                        whereBuider.Append(" and Material like @Material ");
                    }

                    if (!string.IsNullOrEmpty(req.Sender))
                    {
                        req.Sender = "%" + req.Sender + "%";
                        whereBuider.Append(" and Sender like @Sender ");
                    }

                    if (!string.IsNullOrEmpty(req.Receiver))
                    {
                        req.Receiver = "%" + req.Receiver + "%";
                        whereBuider.Append(" and Receiver like @Receiver ");
                    }
                }

                StringBuilder sqlBuider = new StringBuilder();
                sqlBuider.Append(" select *  from  ( select *,ROW_NUMBER() over(order by CreateDate) as rank from CardManager ")
                    .Append(whereBuider.ToString()).Append(")as tab ")
                    .Append(" where rank between " + (rowStart + 1) + " and " + rowEnd + "");
                sqlBuider.Append(" order by CreateDate desc ;select count(1) from CardManager ").Append(whereBuider.ToString());
                sql = sqlBuider.ToString();
            }

            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                var res = conn.QueryMultiple(sql, req);
                return new CardManagerQueryRes()
                {
                    CardManagerList = res.Read<CardManagerEntity>().ToList(),
                    Total = res.Read<int>().FirstOrDefault()
                };
            }
        }

        /// <summary>
        /// 根据车号查询列表
        /// </summary>
        /// <param name="plateNum">车号</param>
        /// <returns></returns>
        public IEnumerable<CardManagerEntity>Query(string  plateNum)
        {
            string sql = " select * from CardManager where State=@State";
            if(!string.IsNullOrEmpty(plateNum))
            {
                plateNum = "%" + plateNum + "%";
                sql += " and PlateNumber like @PlateNumber";
            }
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Query<CardManagerEntity>(sql,new { PlateNumber =plateNum, State =0});
            }
        }

        /// <summary>
        /// 有效卡号记录是否已经存在
        /// </summary>
        /// <param name="cardId"></param>
        /// <returns></returns>
        public bool IsExistCard(string rFNo)
        {
            string sql = " select count(1) from  CardManager where RFNo=@RFNo and State=@State and IsDel=@IsDel";
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
               return  conn.ExecuteScalar<int>(sql,new { RFNo  = rFNo , State=0, IsDel='否'}) > 0 ?true:false;
            }
        }

        /// <summary>
        /// 有效车号记录是否已经存在
        /// </summary>
        /// <param name="cardId"></param>
        /// <returns></returns>
        public bool IsExistPlateNum(string plateNum)
        {
            string sql = " select count(1) from  CardManager where PlateNumber=@PlateNumber and State=@State and IsDel=@IsDel";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { PlateNumber = plateNum, State = 0, IsDel = '否' }) > 0 ? true : false;
            }
        }

        /// <summary>
        /// 有效车号(不包括指定卡号)是否已经存在
        /// </summary>
        /// <param name="rFNo"></param>
        /// <param name="platenum"></param>
        /// <returns></returns>
        public bool IsExistPlatenumExceptRFNo(string rFNo, string platenum)
        {
            string sql = "select count(1) from  CardManager  where PlateNumber=@PlateNumber and RFNo<>@RFNo and State=@State and IsDel=@IsDel ";
            using(IDbConnection  conn = SessionFactory.CreateConnection())
            {
                return conn.ExecuteScalar<int>(sql,new { PlateNumber=platenum, RFNo = rFNo, State = 0, IsDel = '否' }) > 0 ? true:false;
            }
        }

        /// <summary>
        /// 有效车号(不包括指定卡号)是否已经存在
        /// </summary>
        /// <param name="rFNo"></param>
        /// <param name="platenum"></param>
        /// <returns></returns>
        public bool IsExistRFNoExceptPlatenum(string rFNo, string platenum)
        {
            string sql = "select count(1) from  CardManager  where PlateNumber<>@PlateNumber and RFNo=@RFNo and State=@State and IsDel=@IsDel and IsEnd=@IsEnd ";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { PlateNumber = platenum, RFNo = rFNo, State = 0, IsDel = '否' , IsEnd  =0}) > 0 ? true : false;
            }
        }

        /// <summary>
        /// 根据Id查找
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CardManagerEntity GetById(string id)
        {
            string sql = " select * from  CardManager where Id=@Id";
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Query<CardManagerEntity>(sql,new {Id=id }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 根据卡号查找数据
        /// </summary>
        /// <param name="rfNo">卡号</param>
        /// <returns></returns>
        public CardManagerEntity GetCardByRfNo(string rfNo)
        {
            string sql = "select * from CardManager where  RFNo=@RFNo and IsEnd=@IsEnd ";
            using (IDbConnection conn =SessionFactory.CreateConnection())
            {
                return conn.Query<CardManagerEntity>(sql,new { RFNo=rfNo, IsEnd=false }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 根据卡号查找数据
        /// </summary>
        /// <param name="rfNo">卡号</param>
        /// <returns></returns>
        public CardManagerEntity GetValidCardByRfNo(string rfNo)
        {
            string sql = "select * from CardManager where  RFNo=@RFNo and IsEnd=@IsEnd  and State < @state ";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Query<CardManagerEntity>(sql, new { RFNo = rfNo, IsEnd = false, state=3 }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 根据车号查找数据
        /// </summary>
        /// <param name="rfNo">卡号</param>
        /// <returns></returns>
        public CardManagerEntity GetValidCardByPlateNum(string plateNum)
        {
            string sql = "select * from CardManager where  PlateNumber=@plateNum and IsEnd=@IsEnd  and State < @state ";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Query<CardManagerEntity>(sql, new { PlateNumber = plateNum, IsEnd = false, state = 3 }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public  bool Insert(CardManagerEntity entity)
        {
            using(IDbConnection conn =SessionFactory.CreateConnection())
            {
                conn.Insert(entity);
                return true;
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Update(CardManagerEntity entity)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                conn.Update(entity);
                return true;
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Del(string id)
        {
            string sql = " update CardManager set IsDel=@IsDel where Id=@Id";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
               return   conn.Execute(sql, new { Id = id, IsDel="是" }) > 0 ? true:false;
            }
        }


        /// <summary>
        /// 补卡
        /// </summary>
        /// <param name="id">主键编号</param>
        /// <param name="newRfNo">新卡号</param>
        /// <returns></returns>
        public bool SupplyCardNo(string id,string newRfNo)
        {
            string sql = "update CardManager set RFNo=@RFNo where Id=@Id";
           using(IDbConnection conn =SessionFactory.CreateConnection())
            {
                return conn.Execute(sql, new { RFNo = newRfNo, Id = id }) > 0 ? true : false;
            }
        }

        /// <summary>
        /// 销卡
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public bool UnbindCard(string id)
        {
            string sql = "update CardManager set IsEnd=@IsEnd where Id=@Id";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Execute(sql, new { IsEnd = true, Id = id }) > 0 ? true : false;
            }
        }

        /// <summary>
        /// 获取待过磅的卡数量
        /// </summary>
        /// <returns></returns>
        public int GetRedeayCardCount()
        {
            string sql = "select count(1) from CardManager where State=@State and  IsDel=@IsDel";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { State = 0, IsDel = "否" }) ;
            }
        }
    }
}
